package com.elite.groovy.callback

import com.elite.groovy.kh.KHExcelUtil
import com.elite.groovy.kh.KHUtil
import com.elite.groovy.kh.LogToDB
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.Util
import com.wisdge.dataservice.Result
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.SQLException
import java.text.SimpleDateFormat

/**
 * @projectName: ngsapi_cs
 * @package: cn.com.callback
 * @className: apiDataExport
 * @author: LiJT
 * @description: 数据导入服务
 * @date: 2022-12-19 14:03
 * @version: 1.0
 */

public class apiDataImport implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiDataImport.class);

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");

        long startTime = System.currentTimeMillis();
        String getMethod = request.getMethod();

        String callBody = ""; // 入参请求体
        JSONObject resJson = new JSONObject();
        String import_type = ""; // 导入类型

        try {

            // 调用类型校验,此接口仅支持GET
            log.debug("getMethod:" + getMethod);
            if (!getMethod.equals("POST")) {
                log.error("HTTP请求调用类型错误，接口调用失败：" + getMethod);
                return JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.WARN, "HTTP请求调用类型错误，接口调用失败：" + getMethod)));
            }

            // 获取所有Header
            Map<String, String> mapHeader = new HashMap<String, String>();
            Enumeration headerNames = request.getHeaderNames();
            while (headerNames.hasMoreElements()) {
                String key = (String) headerNames.nextElement();
                String value = request.getHeader(key);
                log.debug("Call Header-" + key + ":" + value);

                mapHeader.put(key, value);
            }

            // 获取所有参数名
            Map<String, String> mapParams = new HashMap<String, String>();
            Enumeration ep = request.getParameterNames();
            while (ep.hasMoreElements()) {
                String key = (String) ep.nextElement();
                String value = request.getParameter(key);
                log.debug("Call Parameter-" + key + ":" + value);
                mapParams.put(key, value);

            }

            // 获取调用数据
            callBody = Util.getRequestBody(request, "UTF-8");
            log.info("接收调用 获取调用数据：" + callBody);

            // 获取调用报文
            JSONObject callJson = JSONObject.fromObject(callBody);

            // 获取导入类型
            import_type = KHUtil.GetSFJV(callJson, "import_type"); // 导入类型
            log.info("接收调用 获取导入类型：" + import_type);

            if (import_type == "业务员维护导入") {
                resJson = doSyncInTable(eliteBean, callJson);

            } else if (import_type == "业务员维护导入_FILE") {
                resJson = doSyncFileInTable(eliteBean, callJson);

            } else {
                resJson = JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.WARN, "获取导入类型不支持，导入类型：" + import_type)));
            }
        } catch (Exception e) {
            log.error(Util.getERRO(e));
            resJson = JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.WARN, "HTTP请求调用类型错误，接口调用失败：" + getMethod)));
        } finally {
            LogToDB.toDB(eliteBean, "dataSource", "数据导入服务[数组]", "apiDataExport", callBody, resJson.toString(), "本次发送总耗时：" + (System.currentTimeMillis() - startTime).toString(), import_type);

        }

        return resJson;

    }


    public static JSONObject doSyncFileInTable(EliteBean eliteBean, JSONObject callJson) {

        long startTime = System.currentTimeMillis();

        String file_Path = callJson.getString("file_path");
        String table_SQL = callJson.getString("table_sql");
        int params_Count = countOccurrences(table_SQL, "?");
        log.debug("当前doSyncInTable #Table_SQL:" + table_SQL + " 参数(数量):" + params_Count);


        List<List<List<String>>> file_Datas = KHExcelUtil.readExcel(file_Path, 1, 1);

        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());

        List<List<String>> table_Datas = file_Datas.get(0);
        log.debug(file_Path)
        log.debug(file_Datas.toString())
        int totolNum = 0;
        int loopNum = 0;
        List<String[]> paramListCus = new ArrayList<>();
        for (int i = 0; i < table_Datas.size(); i++) {

            List<String> row_Data = table_Datas.get(i);
            String[] cusArr = new String[params_Count];

            if (row_Data.size() < params_Count) {
                log.warn("行中数据列缺失,数据放弃处理 行号:" + i);
                continue;
            }

            for (int j = 0; j < params_Count; j++) {
                cusArr[j] = row_Data.get(j);
            }

            paramListCus.add(cusArr);
            loopNum++;
            totolNum++;

        }



        // 4.批量写入
        long dbStartTime = System.currentTimeMillis();
        long costTime = countOccurrences(table_SQL, paramListCus);
        /*jt.batchUpdate(table_SQL, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String[] oneRow = paramListCus.get(i);
                ps.setObject(1, oneRow[0]);
                ps.setObject(2, oneRow[1]);
                ps.setObject(3, oneRow[2]);
                ps.setObject(4, oneRow[3]);
                ps.setObject(5, oneRow[4]);
                ps.setObject(6, oneRow[5]);
                ps.setObject(7, oneRow[6]);
                ps.setObject(8, oneRow[7]);
            }

            @Override
            public int getBatchSize() {
                // 必须有值,一般是需要写入的数量
                return paramListCus.size();
            }
        });*/
        return JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.SUCCESS, "接口处理完成 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime).toString() + " 总数量:" + totolNum)));

    }

    public static JSONObject doSyncInTable(EliteBean eliteBean, JSONObject callJson) {

        long startTime = System.currentTimeMillis();
        try {
            int Db_Batch_Num = 500;
            long dbStartTime = System.currentTimeMillis();

            JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());

            String table_SQL = callJson.getString("table_sql");
            int params_Count = countOccurrences(table_SQL, "?");
            log.debug("当前doSyncInTable #Table_SQL:" + table_SQL + " 参数(数量):" + params_Count);

            JSONArray table_Datas = callJson.getJSONArray("table_data");

            int totolNum = 0;
            int loopNum = 0;
            List<Object[]> paramListCus = new ArrayList<>();
            for (int i = 0; i < table_Datas.size(); i++) {

                String[] cusArr = new String[params_Count];
                for (int j = 0; j < params_Count; j++) {
                    cusArr[j] = table_Datas.getJSONArray(i).get(j).getClass().getName().equals("net.sf.json.JSONNull") ? "" : table_Datas.getJSONArray(i).getString(j);
                }

                paramListCus.add(cusArr);
                loopNum++;
                totolNum++;

                // 如果当前批次已经到 Db_Batch_Num 提交一次DB
                if (loopNum >= Db_Batch_Num) {
                    dbStartTime = System.currentTimeMillis();
                    jt.batchUpdate(table_SQL, paramListCus);
                    log.debug("当前任务数据 #当前批次已经到" + loopNum + " 提交一次DB End 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime).toString() + " 当前已同步:" + totolNum);

                    paramListCus.clear();

                    loopNum = 0;
                }

            }

            // 最后批次未达到200提交一次DB
            if (loopNum > 0) {
                dbStartTime = System.currentTimeMillis();
                jt.batchUpdate(table_SQL, paramListCus);
                paramListCus.clear();

                log.debug("当前任务数据 #最后批次未达到Db_Batch_Num提交一次DB:" + loopNum + " 提交一次DB End 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime).toString() + " 当前已同步:" + totolNum);
            }
            return JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.SUCCESS, "接口处理完成 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime).toString() + " 总数量:" + totolNum)));

        } catch (Exception e) {
            log.error("apiDataImport-doSyncInTable HTTP请求调用错误，接口处理异常：" + e.getMessage(), e);
            return JSONObject.fromObject(com.alibaba.fastjson.JSONObject.toJSONString(new Result(Result.ERROR, "apiDataImport-doSyncInTable HTTP请求调用错误，接口处理异常")));

        }

    }

    public static int countOccurrences(String str, String subStr) {
        int count = 0;
        int index = 0;
        while ((index = str.indexOf(subStr, index)) != -1) {
            count++;
            index += subStr.length();
        }
        return count;
    }

    public static long countOccurrences(String insertSQL, List<String[]> paramListCus) {
        String url = "jdbc:mysql://172.16.31.35:3306/davco_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&allowPublicKeyRetrieval=true";
        String user = "root";
        String password = "u4RtYN29rbb%z^Ru";

        long startTime = System.currentTimeMillis();
        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            conn.setAutoCommit(false); // 禁用自动提交

            // 批量插入100条数据
            for (int i = 0; i < paramListCus.size(); i++) {
                String[] oneRow = paramListCus.get(i);
                pstmt.setObject(1, oneRow[0]);
                pstmt.setObject(2, oneRow[1]);
                pstmt.setObject(3, oneRow[2]);
                pstmt.setObject(4, oneRow[3]);
                pstmt.setObject(5, oneRow[4]);
                pstmt.setObject(6, oneRow[5]);
                pstmt.setObject(7, oneRow[6]);
                pstmt.setObject(8, oneRow[7]);
                pstmt.addBatch(); // 添加到批处理
            }

            // 执行批量操作
            pstmt.executeBatch();

            // 提交事务
            conn.commit();

            System.out.println("Batch insertion completed successfully 耗时(毫秒):" + (System.currentTimeMillis() - startTime));

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return (System.currentTimeMillis() - startTime);
    }
}